﻿Public Class clsPWck
    Public Function getAllList(ty As String) As DataTable
        Dim objMstGjbList As New clsGjbMst
        Dim dtGjb As DataTable = objMstGjbList.selectAll()
        If dtGjb.Rows.Count > 0 Then
            dtGjb.Columns.Add("oper_type")
            dtGjb.Columns.Add("validity_name")
            For Each row As DataRow In dtGjb.Rows
                row("oper_type") = "骨架板"
                If row("validity") = "0" Then
                    row("validity_name") = "无效"
                Else
                    row("validity_name") = "有效"
                End If
            Next
        End If
        Dim objMstFlbList As New clsFlbMst
        Dim dtFlb As DataTable = objMstFlbList.selectAll()
        If dtFlb.Rows.Count > 0 Then
            dtFlb.Columns.Add("oper_type")
            dtFlb.Columns.Add("validity_name")
            For Each row As DataRow In dtFlb.Rows
                row("oper_type") = "法兰板"
                If row("validity") = "0" Then
                    row("validity_name") = "无效"
                Else
                    row("validity_name") = "有效"
                End If
            Next
        End If
        Dim dt As DataTable = New DataTable
        If ty <> "1" Then
            dt.Merge(dtGjb)
        End If
        If ty <> "0" Then
            dt.Merge(dtFlb)
        End If
        If IsNothing(dt) Then
            getAllList = New DataTable
        Else
            getAllList = dt
        End If
    End Function

    Public Function getAllPwCkList(oper_type As String) As DataTable
        Dim szSql As String = ""
        szSql &= "select a.*,b.oper_type,b.pw_date,b.into_date,b.assist_marker from sf.b_pw_ck a "
        szSql &= " left join sf.b_pw_ck_in_details b "
        szSql &= " on a.oper_type=b.oper_type and a.type_value = b.type_value and a.batch_no=b.batch_no"
        szSql &= " where 1 = 1 and a.cur_num > 0 "
        If oper_type <> "-1" Then
            szSql &= " and b.oper_type = '" + oper_type + "'"
        End If
        szSql &= " group by a.sys_id,a.batch_no,a.oper_type,a.type_value,a.material_code,a.cur_num,a.update_time,b.oper_type,b.pw_date,b.into_date,b.assist_marker "
        szSql &= " order by b.pw_date,b.into_date "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            getAllPwCkList = New DataTable
        Else
            getAllPwCkList = dt
        End If
    End Function

    Public Function insert(code As String, en As entitypw_rk) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            sql &= "INSERT INTO sf.b_pw_ck_in_details(ck_in_code, batch_no, oper_type, type_value, material_code, pw_date, into_date, into_num, location, update_time, assist_marker, validity) "
            sql &= "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            With en
                rtn = DBacs.ExecuteUpdateSql(sql, -1, code, .batch_no, .oper_type, .type_value, .material_code, .pw_date, .into_date, .into_num, .location, Now(), .assist_marker, .validity)
            End With
            sql = ""
            sql &= " select * from sf.b_pw_ck where batch_no = ? and oper_type = ? and type_value = ? and material_code = ? "
            Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, en.batch_no, en.oper_type, en.type_value, en.material_code)
            sql = ""
            If dt.Rows.Count = 0 Then
                sql &= "INSERT INTO sf.b_pw_ck (batch_no, oper_type, type_value, cur_num, material_code, update_time) "
                sql &= "VALUES ( ?, ?, ?, ?, ?, ?)"
                DBacs.ExecuteUpdateSql(sql, -1, en.batch_no, en.oper_type, en.type_value, en.into_num, en.material_code, Now())
            ElseIf dt.Rows.Count = 1 Then
                sql &= "UPDATE sf.b_pw_ck  "
                sql &= " SET cur_num = cur_num + ?, update_time=? "
                sql &= " WHERE batch_no = ? and oper_type = ? and type_value = ? and material_code = ?"
                DBacs.ExecuteUpdateSql(sql, -1, en.into_num, Now(), en.batch_no, en.oper_type, en.type_value, en.material_code)
            End If
            sql = ""
            If en.oper_type = "1" Then
                sql &= "UPDATE sf.t_mst_flb "
            Else
                sql &= "UPDATE sf.t_mst_gjb "
            End If
            sql &= " SET cur_num = cur_num + ?, update_time=? "
            sql &= " WHERE mst_value = ? and material_code = ?"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.into_num, Now(), en.type_value, en.material_code)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
        Finally
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function insert(code As String, oper_u As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        Try
            sql &= "INSERT INTO sf.b_pw_ck_in( factory_id, ck_in_code, operator, update_time) "
            sql &= "VALUES ( ?, ?, ?, ?)"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "TS", code, oper_u, Now())
        Catch ex As Exception

        End Try
        Return rtn
    End Function

    Public Function update(batch_no As String, rk_num As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            sql &= " select * from sf.b_pw_ck_out_details where batch_no = ? "
            Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, batch_no)
            sql = ""
            If dt.Rows.Count = 0 Then
                sql &= "UPDATE sf.b_pw_ck  "
                sql &= " SET cur_num = ?, update_time = ? "
                sql &= " WHERE batch_no = ? "
                DBacs.ExecuteUpdateSql(sql, -1, rk_num, Now(), batch_no)
            Else
                Dim chuku_num As Integer = 0
                For index = 0 To dt.Rows.Count - 1
                    chuku_num = chuku_num + Integer.Parse(dt.Rows(index)("out_num").ToString().Replace(".00", ""))
                Next
                sql &= "UPDATE sf.b_pw_ck  "
                sql &= " SET cur_num = ?, update_time=? "
                sql &= " WHERE batch_no = ? "
                DBacs.ExecuteUpdateSql(sql, -1, rk_num - chuku_num, Now(), batch_no)
            End If
            sql = "UPDATE sf.b_pw_ck_in_details  "
            sql &= " SET into_num = ?, update_time=? "
            sql &= " WHERE batch_no = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, rk_num, Now(), batch_no)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.CommitTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function getPwCkInList(startTime As String, endTime As String, code As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_pw_ck_in "
        szSql &= "where 1=1 "
        If startTime <> "" Then
            szSql &= " and update_time >= '" + startTime + "'"
        End If
        If endTime <> "" Then
            szSql &= " and update_time <= '" + endTime + "'"
        End If
        If code <> "" Then
            szSql &= " and ck_in_code like '%" + code + "%'"
        End If
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            getPwCkInList = New DataTable
        Else
            getPwCkInList = dt
        End If
    End Function

    Public Function getPwCkInDetailsList(code As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_pw_ck_in_details "
        szSql &= "where 1=1 "
        If code <> "" Then
            szSql &= " and ck_in_code = '" + code + "'"
        End If
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            getPwCkInDetailsList = New DataTable
        Else
            getPwCkInDetailsList = dt
        End If
    End Function

    Public Function getPwCkOutList(startTime As String, endTime As String, code As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_pw_ck_out "
        szSql &= "where 1=1 "
        If startTime <> "" Then
            szSql &= " and update_time >= '" + startTime + "'"
        End If
        If endTime <> "" Then
            szSql &= " and update_time <= '" + endTime + "'"
        End If
        If code <> "" Then
            szSql &= " and ck_out_code like '%" + code + "%'"
        End If
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            getPwCkOutList = New DataTable
        Else
            getPwCkOutList = dt
        End If
    End Function

    Public Function getPwCkOutDetailsList(code As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_pw_ck_out_details "
        szSql &= "where 1=1 "
        If code <> "" Then
            szSql &= " and ck_out_code = '" + code + "'"
        End If
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            getPwCkOutDetailsList = New DataTable
        Else
            getPwCkOutDetailsList = dt
        End If
    End Function

    Public Function updateInDetailsVlidity(oper_type As String, validity As String, sys_id As String, batch_no As String, mst_value As String, into_num As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "update sf.b_pw_ck_in_details set validity = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, validity, sys_id)
            szSql = String.Empty
            szSql &= "UPDATE sf.b_pw_ck "
            szSql &= " SET cur_num = cur_num - ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE batch_no = ? and type_value = ? and oper_type = ? "
            DBacs.ExecuteUpdateSql(szSql, -1, into_num, batch_no, mst_value, oper_type)
            szSql = String.Empty
            If oper_type.Equals("0") Then
                szSql &= "UPDATE sf.t_mst_gjb "
            Else
                szSql &= "UPDATE sf.t_mst_flb "
            End If
            szSql &= " SET cur_num = cur_num - ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE mst_value = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, into_num, mst_value)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function updateOutDetailsVlidity(oper_type As String, validity As String, sys_id As String, batch_no As String, mst_value As String, out_num As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "update sf.b_pw_ck_out_details set validity = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, validity, sys_id)
            szSql = String.Empty
            szSql &= "update sf.b_gt set status = ? where b_pw_ck_out_details_sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "-1", sys_id)
            szSql = String.Empty
            szSql &= "UPDATE sf.b_pw_ck  "
            szSql &= " SET cur_num = cur_num + ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE batch_no = ? and type_value = ? and oper_type = ? "
            DBacs.ExecuteUpdateSql(szSql, -1, out_num, batch_no, mst_value, oper_type)
            szSql = String.Empty
            If oper_type.Equals("0") Then
                szSql &= "UPDATE sf.t_mst_gjb "
            Else
                szSql &= "UPDATE sf.t_mst_flb "
            End If
            szSql &= " SET cur_num = cur_num + ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE mst_value = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, out_num, mst_value)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function


End Class
